﻿using System;
using System.Data;
using System.Data.SqlClient;
using Dtl;
using System.Collections.Generic;
namespace Dal
{
    public class UnitDao
    {
        public static int setUnit(Unit unit, string ambient)
        {
            int insert = 0;
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return 0;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Add_Unit";
            cmd.Parameters.AddWithValue("@cdUnit", unit.cdUnit);
            cmd.Parameters.AddWithValue("@nmUnit",  unit.nmUnit);
            cmd.Parameters.AddWithValue("@nmUnitShort", unit.nmUnitShort);
            cmd.Parameters.AddWithValue("@idObjectType", unit.idObjectType);
            cmd.Parameters.AddWithValue("@idStatus", unit.idStatus);
            cmd.Parameters.AddWithValue("@cdUnitHist", unit.cdUnitHist);            

            try
            {
                cmd.Connection.Open();
                insert = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (SqlException exc) {

                Credentials.writeLogFile("setUnit: " + exc.Message);
                return 0;
            }

            return insert;
        }

        public static DataSet getUnit(short idUnit, string ambient)
        {
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return null;
            }

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Get_Unit";
            cmd.Parameters.AddWithValue("@idUnit", idUnit);
             
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtConversion = new DataTable("Unitdt");

            try
            {
                cmd.Connection.Open();
                da.Fill(dtConversion);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getUnit: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("Unitds");
            ds.Tables.Add(dtConversion);


            return ds;

        }

        public static DataSet listUnit(string ambient)
        {
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return null;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_List_Unit";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtConversion = new DataTable("Unitdt");

            try
            {
                cmd.Connection.Open();
                da.Fill(dtConversion);
                cmd.Connection.Close();

            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("listUnit: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("Unitds");
            ds.Tables.Add(dtConversion);


            return ds;

        }

        public static int editUnit(Unit unit, string ambient)
        {
            int insert = 0;

            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return 0;
            }

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Edit_Unit";
            cmd.Parameters.AddWithValue("@idUnit", unit.idUnit);
            cmd.Parameters.AddWithValue("@cdUnit", unit.cdUnit);
            cmd.Parameters.AddWithValue("@nmUnit", unit.nmUnit);
            cmd.Parameters.AddWithValue("@nmUnitShort", unit.nmUnitShort);
            cmd.Parameters.AddWithValue("@idObjectType", unit.idObjectType);
            cmd.Parameters.AddWithValue("@idStatus", unit.idStatus);
            cmd.Parameters.AddWithValue("@cdUnitHist", unit.cdUnitHist);


            try
            {
                cmd.Connection.Open();
                insert = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (SqlException exc) {

                Credentials.writeLogFile("editUnit: " + exc.Message);
                return 0;
            }

            return insert;
        }

        public static bool SetTransactionUnit(List<Dtl.Unit> unit, string ambient)
        {
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return false;
            }


            SqlTransaction tr = null;
            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(connection);

            try
            {
                string query;
                command.Connection.Open();
                command.Connection.CreateCommand();
                tr = command.Connection.BeginTransaction("TansactionUnit");      
                command.Transaction = tr;
                for (int i = 0; i < unit.Count; i++)
                {

                    query = "";
                    switch (unit[i].typeOperation) 
                    {
                        case 0://INSERTAR
                            query = "INSERT INTO bzUnit([cdUnit],[nmUnit],[nmUnitShort],[idObjectType],[idStatus],[cdUnitHist],[flRemoteDelete],[dtLastModified])" +
                                    "    VALUES" +
                                    "   ('" + unit[i].cdUnit + "', " +
                                    "    '" + unit[i].nmUnit + "', " +
                                    "    '" + unit[i].nmUnitShort + "'," +
                                    "    '" + unit[i].idObjectType + "'," +
                                    "    '" + unit[i].idStatus + "', " +
                                    "    '" + unit[i].cdUnitHist + "'," +
                                    "    0," +
                                    "    GETDATE())";
                            break;

                        case 1://MODIFICAR
                            query = "UPDATE bzUnit" +
                                    "   SET    [cdUnit] = '" + unit[i].cdUnit + "'," +
                                    "          [nmUnit] = '" + unit[i].nmUnit + "'," +
                                    "          [nmUnitShort] = '" + unit[i].nmUnitShort + "'," +
                                    "          [idObjectType] = '" + unit[i].idObjectType + "'," +
                                    "          [idStatus] = '" + unit[i].idStatus + "'," +
                                    "          [cdUnitHist] = '" + unit[i].cdUnitHist + "'," +
                                    "          [flRemoteDelete] = 0," +
                                    "          [dtLastModified] = GETDATE()" +
                                    " WHERE idUnit = '" + unit[i].idUnit + "'";  
                            break;
                    
                    }


                    command.CommandText = query; 
                    command.ExecuteNonQuery();
                }
                tr.Commit();
                command.Connection.Close();
                return true;
            }
            catch (Exception exc)
            {                
                tr.Rollback();
                command.Connection.Close();
                Credentials.writeLogFile("SetTransactionUnit: " + exc.Message);
                return false;
            }

        }

        public static int deleteUnit(int idUnit, string ambient)
        {
            int delete = 0;

            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return 0;
            }

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Delete_Unit";
            cmd.Parameters.AddWithValue("@idUnit", idUnit);
           


            try
            {
                cmd.Connection.Open();
                delete = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (SqlException exc) 
            {
                Credentials.writeLogFile("deleteUnit: " + exc.Message);
                return 0;
            }

            return delete;
        }
    }
}
